using System;
using IWS.Lib.SingleSignOn;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace IWS.Data.Sql.SingleSignOn
{
	internal class DataProvider : IDataProvider
	{
		string con_string;

		public DataProvider()
		{
			//if empty then use default
			this.ConnectionString = this.ConnectionString;
		}
		public DataProvider(string connection_string)
		{
			this.ConnectionString = connection_string;
		}
		public string ConnectionString
		{
			get
			{
				return this.con_string;
			}
			set
			{
				this.con_string = value;
			}
		}

		public static bool CheckDBNull(SqlDataReader reader, string column_name)
		{
			return !reader.IsDBNull(reader.GetOrdinal(column_name));
		}
		public object checkValueNull(string data)
		{
			if (data.Length > 0)
				return data;
			return System.DBNull.Value;
		}
		public object checkValueNull(DateTime data)
		{
			if (data <= new DateTime(1900,1,1))
				return System.DBNull.Value;
			return data;
		}
		public object checkValueNull(Guid data)
		{
			if (data.Equals(Guid.Empty))
				return System.DBNull.Value;
			return data;
		}

		private void LogException(Exception ex)
		{
			//TODO:	Place data provider logging code here. 
		}


		//	Session functions
		public void SaveSession(Session data)
		{
			string sql = "";
			if (!data.Exists())
			{
				//insert
				sql = "insert into [Session] ([ID],[UserID],[LastActiveDate],[ExpirationDate],[Key]) values ("
				 + "@ID"
				 + ", @UserID"
				 + ", @LastActiveDate"
				 + ", @ExpirationDate"
				 + ", @Key"
				+ ")";
			}
			else
			{
				//update
				sql = "update [Session] set "
					+ "[UserID] = @UserID"
					+ ", [LastActiveDate] = @LastActiveDate"
					+ ", [ExpirationDate] = @ExpirationDate"
					+ ", [Key] = @Key"
					+ " where [ID] = @ID";
			}
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@ID",SqlDbType.UniqueIdentifier);
					pID.Value = checkValueNull(data.ID);
					cmd.Parameters.Add(pID);

					SqlParameter pUserID = new SqlParameter("@UserID",SqlDbType.UniqueIdentifier);
					pUserID.Value = checkValueNull(data.UserID);
					cmd.Parameters.Add(pUserID);

					SqlParameter pLastActiveDate = new SqlParameter("@LastActiveDate",SqlDbType.DateTime);
					pLastActiveDate.Value = checkValueNull(data.LastActiveDate);
					cmd.Parameters.Add(pLastActiveDate);

					SqlParameter pExpirationDate = new SqlParameter("@ExpirationDate",SqlDbType.DateTime);
					pExpirationDate.Value = checkValueNull(data.ExpirationDate);
					cmd.Parameters.Add(pExpirationDate);

					SqlParameter pKey = new SqlParameter("@Key",SqlDbType.NVarChar,50);
					pKey.Value = checkValueNull(data.Key);
					cmd.Parameters.Add(pKey);

					objConn.Open();
					cmd.ExecuteNonQuery();
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose
		}
		public Session LoadSession(Guid ID)
		{
			string sql = "select [ID],[UserID],[LastActiveDate],[ExpirationDate],[Key] from [Session] where [ID] = @ID";
			Session data  = new Session();
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@ID",SqlDbType.UniqueIdentifier);
					pID.Value = ID;
					cmd.Parameters.Add(pID);

					objConn.Open();
					SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					if (reader.Read())
					{
						if (CheckDBNull(reader,"ID"))
						{
							data.ID = reader.GetGuid(reader.GetOrdinal("ID"));
						}
						if (CheckDBNull(reader,"UserID"))
						{
							data.UserID = reader.GetGuid(reader.GetOrdinal("UserID"));
						}
						if (CheckDBNull(reader,"LastActiveDate"))
						{
							data.LastActiveDate = reader.GetDateTime(reader.GetOrdinal("LastActiveDate"));
						}
						if (CheckDBNull(reader,"ExpirationDate"))
						{
							data.ExpirationDate = reader.GetDateTime(reader.GetOrdinal("ExpirationDate"));
						}
						if (CheckDBNull(reader,"Key"))
						{
							data.Key = reader.GetString(reader.GetOrdinal("Key"));
						}
					}
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose
			return data;
		}
		public Session LoadSession(string key)
		{
			string sql = "select [ID],[UserID],[LastActiveDate],[ExpirationDate],[Key] from [Session] where [Key] = @Key";
			Session data  = new Session();
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@Key", SqlDbType.NVarChar,50);
					pID.Value = key;
					cmd.Parameters.Add(pID);

					objConn.Open();
					SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					if (reader.Read())
					{
						if (CheckDBNull(reader,"ID"))
						{
							data.ID = reader.GetGuid(reader.GetOrdinal("ID"));
						}
						if (CheckDBNull(reader,"UserID"))
						{
							data.UserID = reader.GetGuid(reader.GetOrdinal("UserID"));
						}
						if (CheckDBNull(reader,"LastActiveDate"))
						{
							data.LastActiveDate = reader.GetDateTime(reader.GetOrdinal("LastActiveDate"));
						}
						if (CheckDBNull(reader,"ExpirationDate"))
						{
							data.ExpirationDate = reader.GetDateTime(reader.GetOrdinal("ExpirationDate"));
						}
						if (CheckDBNull(reader,"Key"))
						{
							data.Key = reader.GetString(reader.GetOrdinal("Key"));
						}
					}
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose
			return data;
		}
		public void RemoveSession(Guid ID)
		{
			string sql = "delete from [Session] where [ID] = @ID";
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@ID",SqlDbType.UniqueIdentifier);
					pID.Value = ID;
					cmd.Parameters.Add(pID);

					objConn.Open();
					cmd.ExecuteNonQuery();
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose
		}
		public int CountSession(Guid ID)
		{
			string sql = "select count(*) as qty from [Session] where [ID] = @ID";
			int qty = 0;
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@ID",SqlDbType.UniqueIdentifier);
					pID.Value = ID;
					cmd.Parameters.Add(pID);

					objConn.Open();
					SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					if (reader.Read())
					{
						qty = reader.GetInt32(reader.GetOrdinal("qty"));
					}
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose

			return qty;
		}
		public int CountSession(string Key)
		{
			string sql = "select count(*) as qty from [Session] where [Key] = @Key";
			int qty = 0;
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;

					SqlParameter pID = new SqlParameter("@Key", SqlDbType.NVarChar,50);
					pID.Value = Key;
					cmd.Parameters.Add(pID);

					objConn.Open();
					SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					if (reader.Read())
					{
						qty = reader.GetInt32(reader.GetOrdinal("qty"));
					}
				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose

			return qty;
		}
		public List<Session> ListAllSession()
		{
			string sql = "select [ID],[UserID],[LastActiveDate],[ExpirationDate],[Key] from [Session]";
			List<Session> list = new List<Session>();
			using (SqlConnection objConn = new SqlConnection(this.ConnectionString))
			{
				try
				{
					SqlCommand cmd = new SqlCommand(sql,objConn);
					cmd.CommandType = CommandType.Text;


					objConn.Open();
					SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						Session data = new Session();
						if (DataProvider.CheckDBNull(reader,"ID"))
						{
							data.ID = reader.GetGuid(reader.GetOrdinal("ID"));
						}
						if (DataProvider.CheckDBNull(reader,"UserID"))
						{
							data.UserID = reader.GetGuid(reader.GetOrdinal("UserID"));
						}
						if (DataProvider.CheckDBNull(reader,"LastActiveDate"))
						{
							data.LastActiveDate = reader.GetDateTime(reader.GetOrdinal("LastActiveDate"));
						}
						if (DataProvider.CheckDBNull(reader,"ExpirationDate"))
						{
							data.ExpirationDate = reader.GetDateTime(reader.GetOrdinal("ExpirationDate"));
						}
						if (DataProvider.CheckDBNull(reader,"Key"))
						{
							data.Key = reader.GetString(reader.GetOrdinal("Key"));
						}
						list.Add(data);
					}

				}
				catch(Exception ex)
				{
					LogException(ex);
					throw new Exception("There has been an error accessing the data. The information has been logged.",ex);
				}
				finally
				{
					objConn.Close();
				}
			} //end dispose
			return list;
		}
	}
}
